Using Parameters and Dynamic Query Logic in Power BI

Course: Microsoft Power BI Training

Topic: Using Parameters and Dynamic Query Logic in Power Query

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

Learning Resource: JustAcademy

1. Introduction to Parameters and Dynamic Query Logic

Power BI is a business intelligence platform that helps organizations connect to data sources, transform information, build data models, and create interactive reports. During data preparation, analysts often need to change filter values, file locations, database settings, date ranges, or other query inputs without rewriting the entire query.

Power Query provides parameters that allow values to be stored separately and reused throughout query transformations. Dynamic query logic uses these values to control how a query behaves under different conditions.

For example, a company may maintain sales records for several regions. Instead of creating a separate query for each region, an analyst can create a Region parameter and use it to filter the data. Changing the parameter from West to North changes the filter applied to the query.

Parameters can also be used to manage source paths, select date thresholds, control numerical filters, and supply arguments to reusable functions. In supported DirectQuery scenarios, dynamic M query parameters can connect report filter selections to parameters used by source queries.

1.1 What Is a Parameter?

A parameter is a named value that can be referenced by a query, transformation, or function. It provides a centralized way to manage information that may need to change.

Examples of parameters include:

• RegionParameter: Stores a region name such as West.
• MinimumSalesParameter: Stores a minimum sales threshold such as 10000.
• StartDateParameter: Stores the beginning date for a reporting period.
• FolderPathParameter: Stores the location of a source folder.
• ServerParameter: Stores a database server name.

1.2 What Is Dynamic Query Logic?

Dynamic query logic allows a query to respond to changing inputs instead of relying entirely on fixed values. A parameter can influence which rows are returned, which transformation is applied, or which source location is referenced.

For example, a fixed filter might return only sales from the West region. A parameter-driven filter can return sales from West, North, South, or East depending on the current parameter value.

2. Learning Objectives

After studying this topic, you will be able to:

• Understand parameters and their role in Power Query.
• Create and configure parameters in Power BI Desktop.
• Use parameters in filters and transformation steps.
• Build dynamic filtering logic with Power Query M.
• Use parameters to manage source paths and environment settings.
• Understand conditional logic using if, then, and else.
• Develop reusable functions that accept parameters.
• Understand the difference between Power Query parameters and dynamic M query parameters.
• Recognize query folding and performance considerations.
• Apply validation and security practices when building dynamic queries.

3. Understanding Power Query Parameters

Power Query parameters are stored as named values that can be reused in one or more queries. Instead of manually editing the same value in multiple places, you can update the parameter and let dependent queries use the new value.

3.1 Important Parameter Properties

3.2 Common Parameter Data Types

Choose a parameter type that matches the value expected by the transformation. A text parameter should not be used directly where a numeric value is required without suitable conversion.

4. Creating a Parameter in Power BI Desktop

Parameters can be created through the Manage Parameters interface in Power Query Editor.

4.1 Step-by-Step Procedure

1. Open Power BI Desktop.
2. Select Home > Transform data to open Power Query Editor.
3. On the Home tab, open Manage Parameters.
4. Select New Parameter.
5. Enter a meaningful parameter name, such as RegionParameter.
6. Add a description explaining its purpose.
7. Choose the appropriate data type.
8. Configure the suggested values as Any Value, List of Values, or Query, depending on your needs.
9. If using a list of values, enter valid options and select a default value where applicable.
10. Set the Current Value.
11. Select OK to create the parameter.
12. Reference the parameter in a query or transformation step.

4.2 Example Parameter Configuration

When the parameter is referenced in a filter, the query uses its current value. Changing the parameter to North and refreshing or reevaluating the query changes the result accordingly.

5. Using Parameters in Query Filters

One of the most common uses of parameters is controlling which rows are returned by a query.

5.1 Example Dataset

Consider a sales table containing the following records:

5.2 Fixed Filter Versus Parameter-Based Filter

A fixed filter always checks a specific value, such as West. A parameter-based filter checks the current value stored in RegionParameter.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [Region] = RegionParameter
    )
in
    FilteredRows

Explanation:

• Source retrieves the sales table from the current Excel workbook.
• Table.SelectRows filters the source records.
• [Region] = RegionParameter compares each row's Region value with the parameter's current value.
• The final result contains rows matching the selected region.

This example assumes that a text parameter named RegionParameter already exists and that the workbook contains a table named SalesData.

5.3 Expected Result When RegionParameter Is West

If the parameter changes to North, the query returns OrderID 102 and OrderID 105 instead.

6. Using Parameters for Numeric Thresholds

Parameters can also control numeric conditions. For example, a company may want to display only orders whose sales value exceeds a configurable threshold.

6.1 Example: Minimum Sales Parameter

Create a parameter named MinimumSalesParameter with the Whole Number data type and a current value of 10000.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredSales = Table.SelectRows(
        Source,
        each [Sales] >= MinimumSalesParameter
    )
in
    FilteredSales

With a parameter value of 10000, records with sales values below 10000 are excluded. Increasing the parameter to 20000 narrows the result to orders with sales of at least 20000.

6.2 Business Uses

• Display orders above a minimum value.
• Exclude products with low sales volume.
• Filter customer records based on an age or balance threshold.
• Identify products whose stock falls below a selected quantity.
• Apply a configurable minimum profit or margin condition.

7. Using Parameters for Date Filters

Date parameters allow an analyst to control the reporting period without editing the filter expression every time.

7.1 Creating Date Parameters

Create two parameters:

• StartDateParameter: A Date parameter with a value such as January 1, 2026.
• EndDateParameter: A Date parameter with a value such as March 31, 2026.

7.2 Example M Code

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ChangedType = Table.TransformColumnTypes(
        Source,
        {{"OrderDate", type date}}
    ),
    FilteredDates = Table.SelectRows(
        ChangedType,
        each [OrderDate] >= StartDateParameter
            and [OrderDate] <= EndDateParameter
    )
in
    FilteredDates

This query retains records whose OrderDate falls within the selected inclusive date range.

7.3 Important Date Considerations

• Make sure the source date column has the correct data type.
• Use Date parameters when comparing date-only values.
• If the source column includes a time component, consider how the end boundary should be handled.
• For timestamp data, an exclusive end boundary can avoid missing records later on the final day. For example, filter timestamps before midnight at the start of the following day.
• Validate that the start date is not later than the end date.

8. Using Parameters to Change File Paths

File paths can change when data is moved between folders, computers, or business environments. A parameter allows the location to be maintained separately from the rest of the transformation logic.

8.1 Example Scenario

A company stores sales files in one of two folders:

• C:\DataFiles\Development
• C:\DataFiles\Production

Instead of editing the source step every time the folder changes, create a text parameter called FolderPathParameter.

8.2 Example M Code

let
    Source = Folder.Files(FolderPathParameter),
    CSVFiles = Table.SelectRows(
        Source,
        each Text.Lower([Extension]) = ".csv"
    )
in
    CSVFiles

The parameter provides the folder path used by the query. Changing the parameter to a valid folder allows the query to read files from that location, assuming the expected file structure and permissions are available.

8.3 Benefits

• Reduces the need to edit source paths manually.
• Helps manage development and production environments.
• Makes queries easier to reuse across similar projects.
• Centralizes configuration information.

Important: Changing a source path does not guarantee that the query will refresh successfully. The new folder must exist, the credentials must be valid, and the expected files and columns must be available.

9. Understanding Dynamic Query Logic

Dynamic query logic means that the result of a query or transformation depends on an input value or condition. Parameters often provide the input, while conditional expressions and M functions implement the logic.

9.1 Common Elements of Dynamic Logic

10. Using if, then, and else in Power Query

Power Query M supports conditional expressions using the structure if condition then result1 else result2. This lets a query return different values based on a condition.

10.1 Example: Classifying Sales

Suppose sales of 20000 or more should be classified as High, while smaller amounts should be classified as Standard.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    AddedCategory = Table.AddColumn(
        Source,
        "SalesCategory",
        each if [Sales] >= 20000
            then "High"
            else "Standard",
        type text
    )
in
    AddedCategory

The query adds a SalesCategory column. Each row is classified according to the Sales value.

10.2 Example: Dynamic Classification Threshold

To make the threshold configurable, create a parameter named SalesThresholdParameter and replace the fixed value with the parameter.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    AddedCategory = Table.AddColumn(
        Source,
        "SalesCategory",
        each if [Sales] >= SalesThresholdParameter
            then "High"
            else "Standard",
        type text
    )
in
    AddedCategory

Changing SalesThresholdParameter changes the classification rule without requiring a rewrite of the expression.

11. Using Multiple Parameters in One Query

Queries can use multiple parameters to control more than one aspect of data preparation. For example, a sales query might filter by region and date range at the same time.

11.1 Example M Code

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ChangedType = Table.TransformColumnTypes(
        Source,
        {
            {"Region", type text},
            {"OrderDate", type date},
            {"Sales", type number}
        }
    ),
    FilteredRows = Table.SelectRows(
        ChangedType,
        each [Region] = RegionParameter
            and [OrderDate] >= StartDateParameter
            and [OrderDate] <= EndDateParameter
    )
in
    FilteredRows

This query uses three parameters:

• RegionParameter controls which region is included.
• StartDateParameter controls the first date in the range.
• EndDateParameter controls the final date in the range.

Using multiple parameters can reduce repeated editing and make the query easier to maintain. Ensure that every parameter is configured with a compatible data type and a valid current value.

12. Using Parameters in Custom Functions

A custom function is reusable logic that accepts one or more arguments and returns a result. Parameters can be used as function arguments to apply the same processing logic to different inputs.

12.1 Why Custom Functions Are Useful

• Reduce repeated transformation code.
• Apply consistent rules to multiple tables or files.
• Make query logic easier to maintain.
• Support reusable data-cleaning operations.
• Separate transformation logic from the input values.

12.2 Example: Create a Reusable Sales Filter Function

The following function accepts a table and a minimum sales value, then returns rows meeting the threshold.

(InputTable as table, MinimumSales as number) as table =>
    Table.SelectRows(
        InputTable,
        each [Sales] >= MinimumSales
    )

Save the expression as a function, for example FilterSales. You can invoke it with a table and a numeric threshold.

FilterSales(SalesData, 15000)

This returns rows from SalesData where Sales is greater than or equal to 15000, provided the table has a numeric Sales column.

12.3 Functions and Parameters

A Power Query parameter is a stored input value. A function argument is a value passed to a function when it is invoked. Parameters can be referenced inside queries and can also be supplied as arguments to functions, but the two concepts are not identical.

13. Using Dynamic Query Logic to Select Between Conditions

Sometimes a query must apply different filters depending on a selected option. For example, an analyst may want to filter sales by region or return all regions.

13.1 Example: Include All Regions or One Region

Create a text parameter named RegionModeParameter with possible values such as All and Selected. Use RegionParameter to specify the selected region.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows =
        if RegionModeParameter = "All" then
            Source
        else
            Table.SelectRows(
                Source,
                each [Region] = RegionParameter
            )
in
    FilteredRows

If RegionModeParameter is All, the query returns the source table without applying a region filter. Otherwise, it returns only the selected region.

In this example, the values All and Selected must match the parameter's configured values exactly. M is case-sensitive, so spelling and capitalization should be consistent.

14. Power Query Parameters vs. Dynamic M Query Parameters

The terms sound similar, but they describe different use cases.

Important: Dynamic M query parameters have specific requirements and limitations. They are not a general-purpose replacement for all Power Query parameters and are not supported by every connector or query mode.

15. Dynamic M Query Parameters in Power BI Reports

Dynamic M query parameters can allow a report user's slicer or filter selection to be passed to a parameter used by a supported DirectQuery query. This can help the source database apply filtering at the appropriate point and can improve performance when designed correctly.

15.1 General Workflow

1. Connect to a supported data source using DirectQuery.
2. Create a valid M query that uses one or more parameters.
3. Create parameter values that match the intended source query inputs.
4. Create a model table or field containing the permitted parameter values.
5. In Model view, select the appropriate field and configure its advanced properties to bind it to the parameter, if supported.
6. Configure single-select or multi-select behavior as required by the query logic.
7. Add the bound field to a slicer or filter in the report.
8. Test the selections and verify that the resulting data matches the expected output.

15.2 Example Scenario

A company stores sales transactions in a supported DirectQuery source. Report viewers need to select a country from a slicer. The selected country is passed to a country parameter used by the query so the source can filter the data.

This can reduce the need to retrieve irrelevant records, but the performance benefit depends on the connector, source system, query design, data volume, and whether filtering is executed efficiently at the source.

15.3 Important Limitations

• Only supported DirectQuery sources and compatible parameter types can be bound.
• Not every filter or slicer interaction is supported.
• Multi-select requires the M query to handle multiple values correctly.
• Dynamic M query parameters do not replace row-level security.
• Changing source locations dynamically has additional Power BI service limitations.
• Validate the behavior in the actual connector and Power BI environment before deployment.

16. Query Folding and Performance

Query folding occurs when Power Query translates supported transformations into operations that can be executed by the underlying data source. For example, a filter may be translated into a SQL WHERE clause so that fewer records need to be returned to Power BI.

16.1 Why Query Folding Matters

• Reduces the amount of data transferred to Power BI.
• Can improve refresh and query performance.
• Allows capable data sources to use their own query engines.
• Can be important for large datasets and DirectQuery solutions.

16.2 Example: Parameterized Filter

let
    Source = Sql.Database("ServerName", "SalesDatabase"),
    SalesTable = Source{[Schema="dbo", Item="Sales"]}[Data],
    FilteredRows = Table.SelectRows(
        SalesTable,
        each [Region] = RegionParameter
    )
in
    FilteredRows

When the connector supports the operation and the query remains foldable, Power Query may push the region filter to the database. The actual folding behavior depends on the source and the transformations used.

16.3 Performance Best Practices

• Filter unnecessary rows early when appropriate.
• Remove columns that are not required downstream.
• Prefer transformations that can fold to the source when possible.
• Inspect query folding indicators or native-query options where available.
• Use Query Diagnostics when investigating slow refresh operations.
• Avoid assuming that every parameter-based transformation folds automatically.

17. Security Considerations for Dynamic Queries

Dynamic query logic must be designed carefully because user-controlled parameter values can affect the query sent to a data source.

17.1 Avoid Unsafe Query String Construction

Concatenating unvalidated text directly into a native SQL or other source query can create injection risks. A malicious value may change the meaning of the query rather than simply filtering the intended data.

Where possible, use connector-supported parameter binding, safe source APIs, stored procedures with parameters, or Power Query operations that can be translated safely to the source. Do not assume that concatenating strings is secure merely because the input originates from a report filter.

17.2 Recommended Security Practices

• Validate parameter values against an approved set of options.
• Use least-privilege database credentials.
• Avoid embedding passwords, tokens, or other secrets in M code.
• Use supported credential management and data-source settings.
• Use row-level security when users must be restricted to specific records.
• Do not treat a parameter or slicer as a security boundary.
• Test source queries using invalid and unexpected inputs.

18. Common Errors and Troubleshooting

18.1 Error: Parameter Not Recognized

Cause: The parameter name is misspelled, does not exist, or is referenced from an incorrect query context.

Solution: Check the parameter name in Manage Parameters and make sure the M expression uses the exact name.

18.2 Error: Data Type Mismatch

Cause: A text parameter is used where a number or date is expected.

Solution: Set the correct parameter type and convert values only when the conversion is valid. Make sure the source column uses a compatible type.

18.3 Error: No Rows Returned

Cause: The current parameter value does not match any source records, or a date range or threshold excludes all rows.

Solution: Verify the current value, check spelling and capitalization, inspect the filter conditions, and test with a known value that should return records.

18.4 Error: File or Folder Not Found

Cause: The path parameter points to a nonexistent location or a location that the refresh environment cannot access.

Solution: Verify the path, permissions, credentials, and expected folder structure. Consider whether the Power BI service refresh environment can access the source.

18.5 Error: Query Does Not Update as Expected

Cause: A parameter was changed but the query was not reevaluated, a dependent query is not configured as expected, or a filter is using a fixed value instead of the parameter.

Solution: Check the relevant Applied Steps and M code, verify the parameter's current value, and refresh or reevaluate the query.

18.6 Error: Query Folding Is Lost

Cause: A transformation or expression is not supported for folding by the connector, or an earlier operation prevents subsequent steps from folding.

Solution: Inspect the folding behavior, simplify the transformations, and test whether the filter can be pushed to the source. Do not assume that changing the order of steps will always restore folding.

18.7 Error: Dynamic M Parameter Binding Is Missing

Cause: The selected source, model field, parameter type, or query mode may not meet the feature's requirements.

Solution: Verify that the source uses a supported DirectQuery configuration, that the parameter exists, and that the field's data type is compatible. Review current Microsoft documentation for connector-specific restrictions.

19. Best Practices for Parameters and Dynamic Query Logic

• Use descriptive names: Choose names such as StartDateParameter or MinimumSalesParameter.
• Document each parameter: Explain its purpose, expected format, and valid values.
• Use correct data types: Match the parameter type to the source value and transformation.
• Validate values: Restrict values where possible and handle invalid selections appropriately.
• Keep logic reusable: Use parameters and custom functions to avoid repeated expressions.
• Separate configuration from logic: Keep source paths and configurable thresholds outside fixed transformation expressions where practical.
• Test edge cases: Test empty results, boundary dates, null values, invalid inputs, and unexpected data types.
• Check refresh behavior: Confirm that parameter changes work in both Power BI Desktop and the intended deployment environment.
• Preserve security: Use safe query construction and appropriate access controls.
• Review query folding: Verify source-side filtering when performance matters.
• Understand the parameter type: A Power Query parameter is not automatically a report slicer.
• Use dynamic M parameters selectively: Apply them only where the source, query mode, and report interactions support the feature.

20. Practical Exercise

Use a sales dataset containing OrderID, OrderDate, Region, Product, and Sales.

Tasks

1. Import the sales dataset into Power Query Editor.
2. Create a text parameter named RegionParameter.
3. Set the current value to West.
4. Create a numeric parameter named MinimumSalesParameter with a value of 10000.
5. Create StartDateParameter and EndDateParameter using the Date data type.
6. Apply a filter based on RegionParameter.
7. Apply a numeric filter based on MinimumSalesParameter.
8. Apply a date filter using the selected date range.
9. Change the region and threshold values to test the query.
10. Check the results and document the effect of each parameter.

Expected Outcome

The final query should return only records that match the selected region, meet the minimum sales requirement, and fall within the specified date range.

21. Mini Project: Build a Configurable Sales Query

Project Objective: Develop a reusable Power Query solution in which important filtering criteria can be changed through parameters.

Project Requirements

• Connect to a sales source, such as an Excel table or database table.
• Create a Region parameter with suggested values.
• Create a MinimumSales parameter.
• Create StartDate and EndDate parameters.
• Use the parameters in the query's filtering logic.
• Add a conditional column to classify sales as High or Standard using a configurable threshold.
• Review data types and handle invalid or missing values.
• Check whether the transformations fold to the source when applicable.
• Test multiple combinations of parameter values.
• Load the results into Power BI and create a sales summary report.

Expected Deliverables

1. A Power Query solution using multiple parameters.
2. Documented M expressions.
3. A report based on the filtered sales data.
4. A short explanation of performance, validation, and security considerations.

22. Interview Questions and Answers

Q1. What is a parameter in Power Query?

Answer: A parameter is a named value that can be reused in query steps, source settings, filters, or functions.

Q2. Why are parameters useful in Power BI?

Answer: They make queries easier to configure and maintain by allowing selected values to change without rewriting the entire query.

Q3. How do you create a parameter?

Answer: Open Power Query Editor, select Home, open Manage Parameters, choose New Parameter, and configure its name, type, suggested values, and current value.

Q4. Can parameters be used in filters?

Answer: Yes. A parameter can supply the value used by a filter expression such as a region comparison, date boundary, or numeric threshold.

Q5. What is dynamic query logic?

Answer: Dynamic query logic allows query results or transformations to change based on parameters, conditions, or other inputs.

Q6. What is the purpose of if, then, and else in M?

Answer: They form a conditional expression that returns one result when a condition is true and another when it is false.

Q7. Can one query use multiple parameters?

Answer: Yes. A query can use multiple parameters to control region, date range, thresholds, source settings, or other supported inputs.

Q8. What is a custom function in Power Query?

Answer: A custom function is reusable logic that accepts arguments and returns a result. It can be used to apply the same transformation to different inputs.

Q9. What is the difference between a Power Query parameter and a dynamic M query parameter?

Answer: A Power Query parameter is a reusable value used in query preparation. A dynamic M query parameter can connect supported report filter selections to parameters used by compatible DirectQuery source queries.

Q10. What is query folding?

Answer: Query folding is the process by which Power Query translates supported transformations into operations that execute at the data source.

Q11. Why might a parameter-based query return no data?

Answer: The current parameter value may not match the source data, the filter may be too restrictive, or the parameter and source column may have incompatible types.

Q12. Can parameters be used to change file paths?

Answer: Yes. A text parameter can provide a file or folder path used by a supported source function, provided the target location is accessible.

Q13. Do Power Query parameters automatically appear as report slicers?

Answer: No. Ordinary Power Query parameters do not automatically become interactive report slicers. Dynamic M query parameters support specific slicer-driven scenarios with compatible DirectQuery sources.

Q14. How can dynamic query logic affect security?

Answer: Unsafe concatenation of user-controlled values into native query strings can create injection risks. Use validated inputs, safe parameter-binding mechanisms, and appropriate source permissions.

Q15. How can you improve parameter-driven query performance?

Answer: Use supported source-side filters, inspect query folding, reduce unnecessary columns and rows, and test performance with representative data.

23. Quick Revision Checklist

• A parameter stores a reusable value.
• Parameters have names, types, and current values.
• Manage Parameters is available in Power Query Editor.
• Parameters can control row filters and transformation arguments.
• Parameters can be used for dates, numeric thresholds, file paths, and other supported inputs.
• Dynamic query logic can use conditional expressions and reusable functions.
• Multiple parameters can be combined in one query.
• Custom functions accept arguments and return results.
• Ordinary Power Query parameters do not automatically create report slicers.
• Dynamic M query parameters require compatible sources and configurations.
• Query folding can improve performance when supported.
• Unsafe query string construction can create security risks.
• Test invalid inputs, date boundaries, refresh behavior, and query results.

24. Conclusion

Parameters and dynamic query logic are valuable features for building flexible and maintainable Power BI data preparation solutions. Parameters centralize configurable values, while dynamic expressions determine how queries respond to those values.

By using parameters for filters, date ranges, thresholds, source paths, and reusable functions, analysts can reduce repetitive editing and build adaptable workflows. Dynamic M query parameters extend this idea to supported DirectQuery scenarios where report selections can influence source queries.

For reliable results, always use appropriate data types, validate parameter values, inspect query folding, test refresh behavior, and follow secure query-construction practices. With these techniques, you can create Power Query solutions that are easier to maintain, reuse, and optimize.

25. Additional Learning Resources

• Explore Microsoft Power BI Training at JustAcademy
• Register for a Power BI Course Demo
• Visit JustAcademy
• Microsoft Learn: Power Query Parameters
• Microsoft Learn: Dynamic M Query Parameters in Power BI Desktop